﻿Imports Microsoft.VisualBasic

Public Class items
    Public id_items As Integer
    Public name As String
    Public amount As Integer
    Public description As String
    Public id_image As Integer
    Public id_type As Integer
    Public id_location As Integer
    Public date_a As Date
    Public bit As Boolean

    'Nascimento da variável, ou seja o construtor
    Public Sub New()

        id_items = 0
        name = " "
        amount = 0
        description = " "
        id_image = 0
        id_type = 0
        id_location = 0
        date_a = Now
        bit = False
    End Sub


    Public Property name_propriedade As String
        Get
            Return name
        End Get
        Set(ByVal value As String)
            name = value
        End Set
    End Property

    Function items_insert() As String

        Dim conection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString


        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = conection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()

        Dim bit_sql As Integer = 0
        If bit Then
            bit_sql = 1

        End If

        Dim string_sql As String = " Insert into items(Name,amount,description,id_image,id_type,id_location,date,active) values('" + name + "', " + amount.ToString() + ", '" + description + "', " + id_image.ToString() + ", " + id_type.ToString() + ", " + id_location.ToString() + ", getdate(),'" + bit_sql.ToString() + "' )"
        SqlCommand1.Connection = SqlConnection1
        SqlCommand1.CommandText = string_sql
        SqlCommand1.ExecuteNonQuery()
        SqlConnection1.Close()

        Return string_sql

    End Function

    Function select_from_db() As String

        Dim conection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString


        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = conection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()

        Dim string_sql As String = "select * from items"

        SqlCommand1.Connection = SqlConnection1
        SqlCommand1.CommandText = string_sql
        SqlCommand1.ExecuteNonQuery()
        SqlConnection1.Close()

        Return string_sql
    End Function

    Function nextt() As String
        'se inseriu bem na base de dados
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        Dim string_sql As String = "Select top 1  id_item,name,amount,description,id_image,id_type,id_location, date, active from items where id_item > " + id_items.ToString() + " order by id_item ASC"
        SqlCommand1.Connection = SqlConnection1
        SqlCommand1.CommandText = string_sql

        Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader

        SqlDataReader1 = SqlCommand1.ExecuteReader()
        If SqlDataReader1.Read() Then
            id_items = SqlDataReader1.GetInt32(0)
            name = SqlDataReader1.GetString(1)
            amount = SqlDataReader1.GetInt32(2)
            description = SqlDataReader1.GetString(3)
            id_image = SqlDataReader1.GetInt32(4)
            id_type = SqlDataReader1.GetInt32(5)
            id_location = SqlDataReader1.GetInt32(6)
            date_a = SqlDataReader1.GetDateTime(7)
            bit = SqlDataReader1.GetBoolean(8)



        End If


        SqlConnection1.Close()

        Return string_sql

    End Function

    Function previous(ByVal id_item As String) As String
        'se inseriu bem na base de dados
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        Dim string_sql As String = "Select top 1  id_item,name,amount,description,id_image,id_type,id_location, date, active from items where id_item > " + id_items.ToString() + " order by id_item DESC"
        SqlCommand1.Connection = SqlConnection1
        SqlCommand1.CommandText = string_sql

        Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader

        SqlDataReader1 = SqlCommand1.ExecuteReader()
        If SqlDataReader1.Read() Then
            id_items = SqlDataReader1.GetInt32(0)
            name = SqlDataReader1.GetInt32(1)
            amount = SqlDataReader1.GetInt32(2)
            description = SqlDataReader1.GetString(3)
            id_image = SqlDataReader1.GetInt32(4)
            id_type = SqlDataReader1.GetInt32(5)
            id_location = SqlDataReader1.GetInt32(6)
            date_a = SqlDataReader1.GetDateTime(7)
            bit = SqlDataReader1.GetBoolean(8)

        End If

        SqlConnection1.Close()

        Return string_sql
    End Function

    Function delete() As String
        'se inseriu bem na base de dados
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        Dim string_sql As String = "Delete from items where id_item= " + id_items.ToString() + ""
       

        SqlCommand1.Connection = SqlConnection1
        SqlCommand1.CommandText = string_sql
        SqlCommand1.ExecuteNonQuery()
        SqlConnection1.Close()

        SqlConnection1.Close()

        Return string_sql
    End Function
End Class
